<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1">
<title>SET CONSTRAINTS</title>
   <body>
      <section id="section1">
         <p id="sql_command_desc">Sets constraint check timing for the current transaction.</p>
         <note type="note">Referential integrity syntax (foreign key constraints) is accepted but not
           enforced.</note>
      </section>
      <section id="section2">
         <title>Synopsis</title>
         <codeblock id="sql_command_synopsis">SET CONSTRAINTS { ALL | <varname>name</varname> [, ...] } { DEFERRED | IMMEDIATE }</codeblock>
      </section>
      <section id="section3">
         <title>Description</title>
         <p><codeph>SET CONSTRAINTS</codeph> sets the behavior of constraint checking within the
            current transaction. <codeph>IMMEDIATE</codeph> constraints are checked at the end of
            each statement. <codeph>DEFERRED</codeph> constraints are not checked until transaction
            commit. Each constraint has its own <codeph>IMMEDIATE</codeph> or
               <codeph>DEFERRED</codeph> mode. </p>
         <p>Upon creation, a constraint is given one of three characteristics: <codeph>DEFERRABLE
               INITIALLY DEFERRED</codeph>, <codeph>DEFERRABLE INITIALLY IMMEDIATE</codeph>, or
               <codeph>NOT DEFERRABLE</codeph>. The third class is always <codeph>IMMEDIATE</codeph>
            and is not affected by the <codeph>SET CONSTRAINTS</codeph> command. The first two
            classes start every transaction in the indicated mode, but their behavior can be changed
            within a transaction by <codeph>SET CONSTRAINTS</codeph>. </p>
         <p><codeph>SET CONSTRAINTS</codeph> with a list of constraint names changes the mode of
            just those constraints (which must all be deferrable). Each constraint name can be
            schema-qualified. The current schema search path is used to find the first matching name
            if no schema name is specified. <codeph>SET CONSTRAINTS ALL</codeph> changes the mode of
            all deferrable constraints. </p>
         <p>When <codeph>SET CONSTRAINTS</codeph> changes the mode of a constraint from
               <codeph>DEFERRED</codeph> to <codeph>IMMEDIATE</codeph>, the new mode takes effect
            retroactively: any outstanding data modifications that would have been checked at the
            end of the transaction are instead checked during the execution of the <codeph>SET
               CONSTRAINTS</codeph> command. If any such constraint is violated, the <codeph>SET
               CONSTRAINTS</codeph> fails (and does not change the constraint mode). Thus,
               <codeph>SET CONSTRAINTS</codeph> can be used to force checking of constraints to
            occur at a specific point in a transaction. </p>
         <p>Currently, only <codeph>UNIQUE</codeph>, <codeph>PRIMARY KEY</codeph>,
               <codeph>REFERENCES</codeph> (foreign key), and <codeph>EXCLUDE</codeph> constraints
            are affected by this setting. <codeph>NOT NULL</codeph> and <codeph>CHECK</codeph>
            constraints are always checked immediately when a row is inserted or modified
               (<i>not</i> at the end of the statement). Uniqueness and exclusion constraints that
            have not been declared <codeph>DEFERRABLE</codeph> are also checked immediately. </p>
         <p>The firing of triggers that are declared as "constraint triggers" is also controlled by
            this setting — they fire at the same time that the associated constraint should be
            checked. </p>
      </section>
      <section id="section4">
         <title>Notes</title>
         <p>Because Greenplum Database does not require constraint names to be unique within a
            schema (but only per-table), it is possible that there is more than one match for a
            specified constraint name. In this case <codeph>SET CONSTRAINTS</codeph> will act on all
            matches. For a non-schema-qualified name, once a match or matches have been found in
            some schema in the search path, schemas appearing later in the path are not searched. </p>
         <p>This command only alters the behavior of constraints within the current transaction.
            Issuing this outside of a transaction block emits a warning and otherwise has no effect.
         </p>
      </section>
      <section id="section5">
         <title>Compatibility</title>
         <p>This command complies with the behavior defined in the SQL standard, except for the
            limitation that, in Greenplum Database, it does not apply to <codeph>NOT NULL</codeph>
            and <codeph>CHECK</codeph> constraints. Also, Greenplum Database checks non-deferrable
            uniqueness constraints immediately, not at end of statement as the standard would
            suggest. </p>
      </section>
   </body>
</topic>
